package com.anolesoft.yr.mbus.prod.pack.sql;

public class ProdPackageSQL {

    //新增时查询产品套餐编号是否已经存在
    public static final String QUERY_PROD_PACKAGE_BY_CODE = "select count(*) as count from prod_package a where a.del_flag=0 and a.species_code=#{speciesCode}";

    //新增时查询产品套餐名称是否已经存在
    public static final String QUERY_PROD_PACKAGE_BY_NAME = "select count(*) as count from prod_package a where a.del_flag=0 and a.species_name=#{speciesName}";

    //根据主键查询产品套餐版次
    public static final String QUERY_PROD_PACKAGE_VERSION_BY_UID = "select a.version from prod_package a where a.prod_production_uid=#{prodProductionUid} and a.species_name=#{speciesName}";

    //新增产品套餐信息表
    public static final String SAVE_PROD_PACKAGE = "insert into prod_package(objuid,fac_code,prod_production_uid,species_code,species_name,species_describe,species_approval_state,is_setup,version,remark,c_user_uid,c_time,del_flag) values(#{objuid},#{facCode},#{prodProductionUid},#{speciesCode},#{speciesName},#{speciesDescribe},#{speciesApprovalState},#{isSetup},#{version},#{remark},#{ccUserUid},#{ccTime},#{delFlag})";

    //根据主键查询产品套餐唯一数据
    public static final String QUERY_PROD_PACKAGE_BY_UID = "select a.*,b.prod_name from prod_package a left join prod_production b on b.objuid=a.prod_production_uid where a.objuid=#{objuid}";

    //根据主键查询产品套餐唯一数据
    public static final String QUERY_PROD_PACKAGE_BY_UID2 = "select a.* from prod_package a where a.objuid=#{objuid}";

    //根据主键查询当前产品套餐最高版次
    public static final String QUERY_PROD_PACKAGE_BY_UID3 = "select max(a.version) as version from prod_package a where a.species_name=(select a.species_name from prod_package a where a.objuid=#{objuid})";

    //修改时查询产品套餐编号是否已经存在（不包含本身）
    public static final String QUERY2_PROD_PACKAGE_BY_CODE = "select count(*) as count from prod_package a where a.del_flag=0 and a.species_code=#{speciesCode} and a.species_code<>#{speciesCode2}";

    //修改时查询产品套餐名称是否已经存在（不包含本身）
    public static final String QUERY2_PROD_PACKAGE_BY_NAME = "select count(*) as count from prod_package a where a.del_flag=0 and a.species_name=#{speciesName} and a.species_name<>#{speciesName2}";

    //修改产品套餐数信息表
    public static final String UPDATE_PROD_PACKAGE = "update prod_package a set a.prod_production_uid=#{prodProductionUid},a.species_describe=#{speciesDescribe},a.species_code=#{speciesCode},a.species_name=#{speciesName},a.remark=#{remark},a.m_user_uid=#{mmUserUid},a.m_time=#{mmTime} where a.objuid=#{objuid}";

    //审批产品套餐信息表
    public static final String SET_TIME_PROD_PACKAGE = "update prod_package a set a.is_setup=#{isSetup},a.start_time=#{startTime},a.invalid_time=#{invalidTime} where a.objuid=#{objuid}";

    //删除产品套餐信息表
    public static final String DELETE_PROD_PACKAGE = "update prod_package a set a.del_flag=1 where a.objuid=#{objuid}";

    //提交产品套餐信息表
    public static final String SUBMIT_PROD_PACKAGE = "update prod_package a set a.species_approval_state=1 where a.objuid=#{objuid}";

    //审批通过产品套餐信息表
    public static final String PASS_PROD_PACKAGE = "update prod_package a set a.species_approval_state=2 where a.objuid=#{objuid}";

    //审批驳回产品套餐信息表
    public static final String REJECT_PROD_PACKAGE = "update prod_package a set a.species_approval_state=3 where a.objuid=#{objuid}";

    //根据条件查询产品套餐数据条数
    public static final String QUERY_PROD_PACKAGE_ALL_COUNT = "<script> select count(*) as count from prod_package a left join prod_production b on b.objuid=a.prod_production_uid left join prod_type c on c.objuid=b.prod_type_uid left join fac_employee d on d.objuid=a.c_user_uid where a.del_flag=0 and a.species_approval_state in(0,3) <if test=\"typeName!=''\"> and upper(c.objuid) like upper(concat('%',#{typeName},'%')) </if> <if test=\"prodName!=''\"> and upper(b.prod_name) like upper(concat('%',#{prodName},'%')) </if> <if test=\"speciesName!=''\"> and upper(a.species_name) like upper(concat('%',#{speciesName},'%')) </if> <if test=\"speciesApprovalState!=''\"> and upper(a.species_approval_state) = upper(#{speciesApprovalState}) </if> </script>";

    //根据条件查询产品套餐所有数据
    public static final String QUERY_PROD_PACKAGE_ALL = "<script> select a.objuid,c.type_name,b.prod_name,a.species_code,a.species_name,a.species_describe,a.species_approval_state,a.is_setup,date_format( a.start_time,'%Y-%m-%d') as start_time,date_format( a.invalid_time,'%Y-%m-%d') as invalid_time,a.version,a.remark,d.emp_name as c_user_uid,a.c_time from prod_package a left join prod_production b on b.objuid=a.prod_production_uid left join prod_type c on c.objuid=b.prod_type_uid left join fac_employee d on d.objuid=a.c_user_uid where a.del_flag=0 and a.species_approval_state in(0,3) <if test=\"typeName!=''\"> and upper(c.objuid) like upper(concat('%',#{typeName},'%')) </if> <if test=\"prodName!=''\"> and upper(b.prod_name) like upper(concat('%',#{prodName},'%')) </if> <if test=\"speciesName!=''\"> and upper(a.species_name) like upper(concat('%',#{speciesName},'%')) </if> <if test=\"speciesApprovalState!=''\"> and upper(a.species_approval_state) = upper(#{speciesApprovalState}) </if> order by c.type_name asc, b.prod_name asc, a.species_name asc limit #{page},#{limit} </script>";

    //根据条件查询产品套餐数据条数
    public static final String QUERY_PROD_PACKAGE_SET_TIME_ALL_COUNT = "<script> select count(*) as count from prod_package a left join prod_production b on b.objuid=a.prod_production_uid left join prod_type c on c.objuid=b.prod_type_uid left join fac_employee d on d.objuid=a.c_user_uid where a.del_flag=0 and a.species_approval_state in(2) <if test=\"typeName!=''\"> and upper(c.objuid) like upper(concat('%',#{typeName},'%')) </if> <if test=\"prodName!=''\"> and upper(b.prod_name) like upper(concat('%',#{prodName},'%')) </if> <if test=\"speciesName!=''\"> and upper(a.species_name) like upper(concat('%',#{speciesName},'%')) </if> </script>";

    //根据条件查询产品套餐所有数据
    public static final String QUERY_PROD_PACKAGE_SET_TIME_ALL = "<script> select a.objuid,c.type_name,b.prod_name,a.species_code,a.species_name,a.species_describe,a.species_approval_state,a.is_setup,date_format( a.start_time,'%Y-%m-%d') as start_time,date_format( a.invalid_time,'%Y-%m-%d') as invalid_time,a.version,a.remark,d.emp_name as c_user_uid,a.c_time from prod_package a left join prod_production b on b.objuid=a.prod_production_uid left join prod_type c on c.objuid=b.prod_type_uid left join fac_employee d on d.objuid=a.c_user_uid where a.del_flag=0 and a.species_approval_state in(2) <if test=\"typeName!=''\"> and upper(c.objuid) like upper(concat('%',#{typeName},'%')) </if> <if test=\"prodName!=''\"> and upper(b.prod_name) like upper(concat('%',#{prodName},'%')) </if> <if test=\"speciesName!=''\"> and upper(a.species_name) like upper(concat('%',#{speciesName},'%')) </if> order by c.type_name asc, b.prod_name asc, a.species_name asc limit #{page},#{limit} </script>";

    //根据条件查询产品套餐审批数据条数
    public static final String QUERY_PROD_PACKAGE_APPROVE_ALL_COUNT = "<script> select count(*) as count from prod_package a left join prod_production b on b.objuid=a.prod_production_uid left join prod_type c on c.objuid=b.prod_type_uid left join fac_employee d on d.objuid=a.c_user_uid where a.del_flag=0 and a.species_approval_state in(1) <if test=\"typeName!=''\"> and upper(c.objuid) like upper(concat('%',#{typeName},'%')) </if> <if test=\"prodName!=''\"> and upper(b.prod_name) like upper(concat('%',#{prodName},'%')) </if> <if test=\"speciesName!=''\"> and upper(a.species_name) like upper(concat('%',#{speciesName},'%')) </if> </script>";

    //根据条件查询产品套餐审批所有数据
    public static final String QUERY_PROD_PACKAGE_APPROVE_ALL = "<script> select a.objuid,c.type_name,b.prod_name,a.species_code,a.species_name,a.species_describe,a.species_approval_state,a.is_setup,date_format( a.start_time,'%Y-%m-%d') as start_time,date_format( a.invalid_time,'%Y-%m-%d') as invalid_time,a.version,a.remark,d.emp_name as c_user_uid,a.c_time from prod_package a left join prod_production b on b.objuid=a.prod_production_uid left join prod_type c on c.objuid=b.prod_type_uid left join fac_employee d on d.objuid=a.c_user_uid where a.del_flag=0 and a.species_approval_state in(1) <if test=\"typeName!=''\"> and upper(c.objuid) like upper(concat('%',#{typeName},'%')) </if> <if test=\"prodName!=''\"> and upper(b.prod_name) like upper(concat('%',#{prodName},'%')) </if> <if test=\"speciesName!=''\"> and upper(a.species_name) like upper(concat('%',#{speciesName},'%')) </if> order by c.type_name asc, b.prod_name asc, a.species_name asc limit #{page},#{limit} </script>";

    //根据条件查询产品套餐名称
    public static final String QUERY_PROD_PACKAGE_NAME = "select a.objuid,a.species_name from prod_package a inner join prod_production b on b.objuid=a.prod_production_uid where a.del_flag=0 and b.prod_name=#{prodName} order by a.species_name asc";

    //根据条件查询产品套餐服务项及参数
    public static final String QUERY_PROD_PACKAGE_NAME_VALUE = "select c.service_option_name,d.value from prod_package a inner join prod_package_rel b on b.prod_package_uid=a.objuid inner join prod_package_service c on c.objuid=b.prod_package_service_uid inner join prod_package_service_value d on d.prod_package_service_uid=c.objuid where a.del_flag=0 and a.species_name=#{speciesName} order by c.service_option_name asc, d.value asc";

}
